package cn.ac.cintcm.synonymous;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


public class MSDb {
	private String fileName;
    private Connection access;
    private List<String> table;
	
	public MSDb(String filename) throws ClassNotFoundException, SQLException {
        this.fileName = filename;
        init();
    }


    public List<String> getTableName() throws ClassNotFoundException, SQLException {
        return table;
    }
    private void init() throws ClassNotFoundException, SQLException {
        table = new ArrayList<String>();
        getConnection();
        String[] types = { "TABLE" };
        ResultSet rs = access.getMetaData().getTables(null, null, "%", types);
        while (rs.next()) {
            String name = rs.getString(3);
            table.add(name);
        }
        this.close();
    }

    public Connection getConnection() throws ClassNotFoundException, SQLException {
	    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	    // set this to a MS Access DB you have on your machine
	    StringBuilder url = new StringBuilder("jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=");
	    url.append(System.getProperty("user.dir"));
	    url.append("\\");
	    url.append(fileName);
	    // now we can get the connection from the DriverManager
	    access = (Connection) DriverManager.getConnection(url.toString() ,"","");
	    return access;
	}
	
	public void createTable(String sql)
	{
	    try
	    {
	        Statement stmt = getConnection().createStatement();   
		    stmt.executeUpdate(sql);
		    stmt.close();
	    }
	    catch (SQLException ex)
	    {
	        handleSqlException(ex);
	    } catch (ClassNotFoundException e) {
			e.printStackTrace();
		}	    
	}
    
    public List<NameField> loadCategory(String tableName) throws SQLException {
        String sql = "select distinct(分类) from " + tableName;
        Statement stmt = access.createStatement();   
        ResultSet rs = stmt.executeQuery(sql);
        List<NameField> categories = new ArrayList<NameField>();
        while (rs.next()) {
            String category = rs.getString(1);
            category = trimString(category);
            NameField nf = new NameField();
            if (category != null && !category.trim().equals("")) {
                nf.setName(category);
            } else {
                nf.setName("����");
            }
            categories.add(nf);
        }
        rs.close();
        stmt.close();
        return categories;
    }
    
    public static String trimString(String category) {
        String ret = category;
        if (category != null && category.length() > NameFieldDao.MAX_NAME_SIZE) {
            ret = category.substring(0, NameFieldDao.MAX_NAME_SIZE);
        }
        return ret;
    }


    public List<NameField> loadSource(String tableName) throws SQLException {
        String sql = "select distinct(出处) from " + tableName;
        Statement stmt = access.createStatement();   
        ResultSet rs = stmt.executeQuery(sql);
        List<NameField> categories = new ArrayList<NameField>();
        while (rs.next()) {
            String category = rs.getString(1);
            category = trimString(category);
            NameField nf = new NameField();
            if (category != null && !category.trim().equals("")) {
                nf.setName(category);
            } else {
                nf.setName("����");
            }
            categories.add(nf);
        }
        rs.close();
        stmt.close();
        return categories;
    }
    
    public List<String[]> loadData(String tableName) throws SQLException {
        String sql = "select 正名,异名,英文名,分类,出处 from " + tableName;
        Statement stmt = access.createStatement();   
        ResultSet rs = stmt.executeQuery(sql);
        List<String[]> list = new ArrayList<String[]>();
        while (rs.next()) {
            String[] strs = new String[5];
            for (int i = 0; i < 5; i++) {
                strs[i] = rs.getString(i+1);
                if (i==1) {
                    strs[i] = sortAlias(strs[i]);
                }            
            }
            list.add(strs);
        }
        rs.close();
        stmt.close();
        return list;       
    }
    
	private String sortAlias(String str) {
        String s = null;
        if (str != null && !str.trim().equals((""))) {
            StringBuilder sb = new StringBuilder(); 
            String[] astr = str.split("\\$");
            Arrays.sort(astr);
            for (String a : astr) {
                sb.append(a);
                sb.append("$");
            }
            sb.deleteCharAt(sb.length() - 1);
            s = sb.toString();
        }
        return s;
    }


    public static void handleSqlException(SQLException ex)
	{
		System.out.println("\n--- SQLException caught ---\n");
		while (ex != null)
		{
			System.out.println("Message:   " + ex.getMessage());
			System.out.println("SQLState:  " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
			ex = ex.getNextException();
			System.out.println("");
		}
	}


    public void close() {
        try {
            access.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
